﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.ServiceModel;
using ServiceOfSansar.Models;
using ServiceOfSansar.Auth;
using System.Text;
using System.Net;
using System.IO;
using System.Data;
using System.Data.SqlClient;
namespace ServiceOfSansar
{
    [ErrorHandler]
    class ftpInterface
    {
        #region Database
        private SansarEntities db;
        public ftpInterface()
        {
            db = new SansarEntities();
        }
        ~ftpInterface()
        {
            db.Dispose();
        }
        #endregion
        public bool sendFile(string fName,string fileName)
        {
            try
            {
                //FtpWebRequest request = (FtpWebRequest)WebRequest.Create(Properties.Settings.Default.FtpServer + @"\" + fName);
                FtpWebRequest request = (FtpWebRequest)WebRequest.Create("ftp://192.168.200.10/" + fileName);

                request.Method = WebRequestMethods.Ftp.UploadFile;

                request.Credentials = new NetworkCredential("s4ns4r", "s0ftware");

                //FtpWebResponse response = (FtpWebResponse)request.GetResponse();

                // Copy the contents of the file to the request stream.
                StreamReader sourceStream = new StreamReader(fName);
                byte[] fileContents = Encoding.UTF8.GetBytes(sourceStream.ReadToEnd());
                sourceStream.Close();
                request.ContentLength = fileContents.Length;

                Stream requestStream = request.GetRequestStream();
                requestStream.Write(fileContents, 0, fileContents.Length);
                requestStream.Close();

                FtpWebResponse response = (FtpWebResponse)request.GetResponse();

                Console.WriteLine("Upload File Complete, status {0}", response.StatusDescription);

                response.Close();
                return true;
            }
            catch (Exception e)
            {
                return false;
            }
        }
        public void CreateBillFileAndSend(long bankID)
        {
            //DataTable tbl = new DataTable();
            string fileName = "II09" + DateTime.Now.ToString("yyMMdd") + ".xml";
            string file = @"C:\Sansar\ftpfiles\II09" + DateTime.Now.ToString("yyMMdd") + ".xml";
            if(File.Exists(file)) return;
            SqlConnection con = new SqlConnection("Data Source=" + Properties.Settings.Default.DbServer + ";initial catalog="+Properties.Settings.Default.DbName+";persist security info=True;user id="+Properties.Settings.Default.UserName+";password="+Properties.Settings.Default.Password);

            SqlCommand mySqlCommand = con.CreateCommand();
            mySqlCommand.CommandText = "sp_GetBillList";
            mySqlCommand.CommandType = CommandType.StoredProcedure;
            mySqlCommand.Parameters.Add("@BankID", SqlDbType.BigInt).Value = bankID;

            SqlDataAdapter custDA = new SqlDataAdapter();
            custDA.SelectCommand = mySqlCommand;
            DataSet myDataSet = new DataSet("Customers");
            con.Open();
            custDA.Fill(myDataSet, "Customer");

            if (myDataSet.Tables["Customer"].Rows.Count == 0)
                return;

            myDataSet.WriteXml(file);
            try
            {
                if (sendFile(file, fileName))
                {
                    foreach (DataRow row in myDataSet.Tables["Customer"].Rows)
                    {
                        BillingFile bill = new BillingFile();

                        bill.phone = row["phone"].ToString();
                        bill.contractno = (long)row["contractno"];
                        bill.username = row["username"].ToString();
                        bill.shifr = row["shifr"].ToString();
                        bill.address = row["address"].ToString();
                        bill.prodcode = (Int32)row["prodcode"];
                        bill.lastdate = row["lastdate"].ToString();
                        bill.finishdate = row["finishdate"].ToString();
                        bill.basic = (decimal)row["basic"];
                        bill.total = (decimal)row["total"];
                        bill.txndate = DateTime.Now.ToString("yyyy/MM/dd");
                        bill.bankid = 1;
                        db.BillingFiles.AddObject(bill);
                    }
                    db.SaveChanges();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
        }
        public bool isFileNew(string fName)
        {
            var file = db.DownloadedFiles.FirstOrDefault(w =>  w.filename.Equals(fName));
            if (file == null)
                return true;
            else
                return false;
        }
        public void getFileAndDoTransaction(string fName)
        {
            FtpWebRequest request = (FtpWebRequest)WebRequest.Create(Properties.Settings.Default.FtpServer+@"\"+fName);
            request.Method = WebRequestMethods.Ftp.DownloadFile;

            request.Credentials = new NetworkCredential(Properties.Settings.Default.FtpUserName, Properties.Settings.Default.FtpPassword);

            FtpWebResponse response = (FtpWebResponse)request.GetResponse();

            StreamReader reader = new StreamReader(response.GetResponseStream());
            string filePath=@"c:\"+fName;
            using (StreamWriter destination = new StreamWriter(filePath))
            {
                destination.Write(reader.ReadToEnd());
                destination.Flush();
            }

            //Console.WriteLine(reader.ReadToEnd());
            DataTable tbl = new DataTable("payment");
            tbl.Columns.Add("SHIFR",typeof(string));
            tbl.Columns.Add("TOTAL", typeof(string));
            tbl.Columns.Add("LOSS", typeof(string));
            tbl.Columns.Add("TDATE", typeof(string));
            tbl.Columns.Add("THEYEAR", typeof(string));
            tbl.Columns.Add("THEMONTH", typeof(string));
            tbl.Columns.Add("BRANCH", typeof(string));
            tbl.Columns.Add("CUSTID", typeof(string));
            tbl.ReadXml(filePath);
            
            foreach (DataRow row in tbl.Rows)
            {
                doTransaction(row);
                Console.WriteLine(row[0].ToString() + "-" + row[1].ToString() + " " + row[2].ToString());
            }

            db.DownloadedFiles.AddObject(new DownloadedFile() { txndate=DateTime.Now,filename=fName});
            db.SaveChanges();
            Console.WriteLine("Download Complete, status {0}", response.StatusDescription);
            reader.Close();
            response.Close();
        }
        private BillingTxn getBilling(string billCode)
        {
            BillingTxn bill = new BillingTxn() { ContractNo = 0 };
            string sql = "SELECT a.BillCode,a.ContractNo,c.ProdCode,"
            +" c.Price-c.Price/100*(CASE WHEN DATEDIFF(day, a.EndDate, GETDATE())<="
            +"-(SELECT ItemVal FROM Settings WHERE Item='SALEDAYS') "
            +" THEN (SELECT ItemVal FROM Settings WHERE Item='SALEPERC') " 
            +" ELSE 0 END) Price,a.EndDate,c.[Length],c.Name,a.Balance, "
            + "CASE WHEN a.EndDate>GETDATE() THEN EndDate ELSE GETDATE() END StartDate"
            +" FROM [Contract] a INNER JOIN Product b ON a.CurrProdCode=b.ProdCode "
            +" INNER JOIN Product c ON b.NextProdCode=c.ProdCode WHERE a.BillCode='"+billCode+"'";
            foreach (BillingTxn billing in db.ExecuteStoreQuery<BillingTxn>(sql))
            {
                bill = billing;
            }
            return (bill);
        }
        private Result doTransaction(DataRow row)
        {
            BillingTxn bill=getBilling(row["SHIFR"].ToString());
            BankFileTransaction bankTxn=new BankFileTransaction();
            BankFileTransaction bankTxnErr = new BankFileTransaction();

            decimal txnamount = decimal.Parse(row["TOTAL"].ToString()) - 200;
            if (bill.ContractNo == 0)
            {
                bankTxnErr.ContractNo = 0;
                bankTxnErr.BillCode = row["SHIFR"].ToString();
                bankTxnErr.prodcode = 0;
                bankTxnErr.txnamount = txnamount;
                bankTxnErr.txndate = DateTime.Now;
                bankTxnErr.txndesc = "Гэрээ олдоогүй!";

                db.BankFileTransactions.AddObject(bankTxnErr);
                db.SaveChanges();

                return new Result() { Code = 1, Message = "Geree oldsongui" };
            }
            else
            {
                bankTxn.ContractNo = bill.ContractNo;
                bankTxn.BillCode = row["SHIFR"].ToString();
                bankTxn.prodcode = bill.ProdCode;
                bankTxn.txnamount = txnamount;
                bankTxn.txndate = DateTime.Now;
                bankTxn.txndesc = "Амжилттай!";
            }
            SendContract con = new SendContract()
            {
                ContractNo = bill.ContractNo,
                Balance = decimal.Parse((bill.Balance+(decimal)bill.Price-txnamount).ToString()),
                CurrProdCode = bill.ProdCode,
                EndDate = bill.StartDate.AddMonths(bill.Length),
                StartDate = bill.StartDate
            };
            List<Transaction> txn = new List<Transaction>();
            txn.Add(new Transaction()
            {
                ContractNo = bill.ContractNo,
                ProdCode = bill.ProdCode,
                Quantity = bill.Length,
                StartDate = bill.StartDate,
                EndDate = bill.StartDate.AddMonths(bill.Length),
                Status = 1,
                TxnCategory ="BIL",
                BankNo = 1,
                TxnPrice = bill.Price,
                TxnFirstBalance = bill.Balance,
                TxnDebtAmount = bill.Price,
                TxnAmount=txnamount,
                TxnDate = DateTime.Now,
                TxnDesc = "Биллингээр төлбөр төлөв",
                TxnType = 1200,
                UserNo = 1
            });
            long jrno;
            Transfer tc = new Transfer();
            var res = tc.DoTransaction(txn.ToArray(), new SendTransfer[] { }, con);
            if (res.Code != 0)
            {
                bankTxnErr.txndesc = "Алдаа гарав! "+res.Message;
                db.BankFileTransactions.AddObject(bankTxnErr);
                db.SaveChanges();
            }
            else
            {
                jrno = long.Parse(res.RetData.ToString());
                bankTxn.txndesc = "Амжилттай! (" + jrno.ToString() + ") "+res.Message;
                db.BankFileTransactions.AddObject(bankTxn);
                db.SaveChanges();
            }
            return new Result() {Code=0 };
        }
        public void test()
        {
            Console.WriteLine("inthe test:"+DateTime.Now.ToString());
        }
        public void ftpReadAndTransaction()
        {
            FtpWebRequest request = (FtpWebRequest)WebRequest.Create(Properties.Settings.Default.FtpServer);
            request.Method = WebRequestMethods.Ftp.ListDirectory;

            request.Credentials = new NetworkCredential(Properties.Settings.Default.FtpUserName, Properties.Settings.Default.FtpPassword);

            FtpWebResponse response = (FtpWebResponse)request.GetResponse();

            //Stream responseStream = response.GetResponseStream();
            StreamReader reader = new StreamReader(response.GetResponseStream());
            //reader.
            var list = new List<string>();

            string line;
            while ((line = reader.ReadLine()) != null)
            {
                list.Add(line);
            }
            foreach (string str in list)
            {
                if (str.Substring(0, 10).ToUpper() == "PS09" + DateTime.Now.ToString("yyMMdd"))
                {
                    if (isFileNew(str))
                    {
                        Console.WriteLine(str);
                        getFileAndDoTransaction(str);
                    }
                    
                }
                //else
                    //Console.WriteLine(DateTime.Now.ToString("yyMMdd") + "    " + str.Substring(0, 10).ToUpper());
            }
            //string str=reader.ReadToEnd();
            //Console.WriteLine(str);
            reader.Close();
            response.Close();
            Console.WriteLine("Finished:");
        }
    }
}
